<?php
class TaskSheetModel extends Model{
	protected $Id;
	protected $Name;
	protected $Date;
	protected $Expire;

	function SetId($id){
		return $this->Id=$id;
	}
	
	function SetName($name){
		return $this->Name=$name;
	}
	
	function SetDate($date){
		return $this->Date=$date;
	}

	function SetExpire($expire){
		return $this->Expire=$expire;
	}
	
	function GetId(){
		return $this->Id;
	}
	
	function GetIdFromSqlByName($name){
		$task = $this->db->query("SELECT id FROM task_sheet WHERE name=?", array($name), $fetch = FALSE, $bindType = TRUE);
		return $task[0]['id'];
	}
	
	function GetName(){
		return $this->Name;
	}
	
	function GetDate(){
		return $this->Date;
	}
	
	function GetExpire(){
		return $this->Expire;
	}
	
	function SetIs_Ready($is_ready){
		return $this->Is_Ready=$is_ready;
	}
	
	function GetTotal_Score(){
		return $this->Total_Score;
	}
	
	function SetTotal_Score($total_score){
		return $this->Total_Score=$total_score;
	}
	
	function GetIs_Ready(){
		return $this->Is_Ready;
	}
		
	function SelectTaskSheetFromSql($id){
		$temp = $this->db->query("SELECT * FROM task_sheet WHERE id= ?", array($id), $fetch = FALSE, $bindType = TRUE);
		$this->Id = $temp[0]['id'];
		$this->Name = $temp[0]['name'];
		$this->Date = $temp[0]['date'];
		$this->Total_Score = $temp[0]['total_score'];
		$this->Is_Ready = $temp[0]['is_ready'];
		$this->Expire = $temp[0]['expire'];
	}
	
	function SelectAllTaskSheetFromSql(){
		$temp = $this->db->query("SELECT * FROM task_sheet", array($id), $fetch = FALSE, $bindType = TRUE);
		return $temp;
	}
	
	/*function UpdateTaskSheetInSql(){
		$temp = $this->db->query('UPDATE task_sheet SET name=?, date=?, expire=? WHERE id= ?', array($this->Name, $this->Date, $this->Expire, $this->Id), $fetch = FALSE, $bindType = TRUE);
	}*/
	
	function NewTaskSheetStep1Check($rawdata){
		$sheet['name']=$rawdata['name'];
		if ($rawdata['sheet_type'] == 'upload')
			$sheet['task_count']=1;
		else 
			$sheet['task_count']=$rawdata['task_count'];
		$sheet['total_score']=$rawdata['total_score'];
		$sheet['expire']=$rawdata['expire'];
		$sheet['sheet_type']=$rawdata['sheet_type'];
		$sheet['step1']=1;
		return $sheet;
	}
	
	function NewTaskSheetStep2Check($rawdata, $sheet, $files){
		for ($i=1; $sheet['task_count']>=$i ; $i++){
			if($rawdata[$i.'-type_select'] == 'on') $sheet['tasks'][$i]['type']=0;
			if($rawdata[$i.'-type_checkbox'] == 'on') $sheet['tasks'][$i]['type']=1;
			if($rawdata['sheet_type'] == 'upload') $sheet['tasks'][$i]['type']=2;
			$sheet['tasks'][$i]['answer_count']=$rawdata[$i.'-answer_count'];
			$sheet['tasks'][$i]['score']=$rawdata[$i.'-score'];
			$sheet['tasks'][$i]['question']=$rawdata[$i.'-question'];
			if(!empty($_FILES)) {
				$sheet['tasks'][$i]['picture']=$files[$i.'-picture']['name'];
				//var_dump($sheet['tasks'][1]);
			}
			else{
				$sheet['tasks'][$i]['picture']=$rawdata[$i.'-picture'];
			}
			if(!empty($_FILES)){
				$sheet['tasks'][$i]['temp_picture']='uploads/temp/'.$files[$i.'-picture']['name'];
			}
			else{
				$sheet['tasks'][$i]['temp_picture']=$rawdata[$i.'-picture'];
				$sheet['tasks'][$i]['video_link']=$rawdata[$i.'-video_link'];
			}
		}
		//var_dump($rawdata);
		$sheet['step2']=1;
		return $sheet;
	}	
	
	function NewTaskSheetStep3Check($rawdata, $sheet){
		for ($i=1; $sheet['task_count']>=$i ; $i++){
			for ($j=1; $sheet['tasks'][$i]['answer_count']>=$j ; $j++){
				$sheet['tasks'][$i]['answers'][$j]['answer']=$rawdata[$i.'-'.$j.'-answer'];
				if ($rawdata[$i.'-'.$j.'-is_correct'] == 'on')
					{$sheet['tasks'][$i]['answers'][$j]['is_correct']=1;}
				if ($rawdata[$i.'-'.$j.'-is_correct'] == 'off')
					{$sheet['tasks'][$i]['answers'][$j]['is_correct']=0;}
			}		
		}
		$sheet['step3']=1;
		return $sheet;
	}	
	
	function NewTaskSheetStep4Check($sheet){
		try{
			//felépít task_sheet
			$temp[0] = 'INSERT INTO `task_sheet` (`id`, `name`, `total_score`, `date`, `expire`) VALUES (NULL,\''.$sheet['name'].'\',\''.$sheet['total_score'].'\',CURRENT_TIMESTAMP,\''.$sheet['expire'].'\')';
			
			//beilleszt task_sheet
			try{
				$stmt = $this->db->sqldb->prepare($temp[0]);
				$stmt->execute();
			}catch(PDOException $error){
				$error[0]=$error;
			}
			
			//felépít task
			$task_sheet_query = $this->db->query("SELECT id FROM task_sheet WHERE name=?", array($sheet['name']), $fetch = FALSE, $bindType = TRUE);
			$task_sheet_id = $task_sheet_query[0]['id'];
			$temp[1] = 'INSERT INTO `task` (`sheet_id` ,`number` ,`description` ,`is_button` ,`is_checkbox` ,`is_upload` ,`total_checkbox` ,`correct_checkbox` ,`score` ) VALUES ';
			for ($i=1; $sheet['task_count']>=$i ; $i++){
				if($sheet['tasks'][$i]['type'] == 0) {$type['button']=1;$type['checkbox']=0;$type['upload']=0;}
				if($sheet['tasks'][$i]['type'] == 1) {$type['button']=0;$type['checkbox']=1;$type['upload']=0;}
				if($sheet['tasks'][$i]['type'] == 2) {$type['button']=0;$type['checkbox']=0;$type['upload']=1;}
				$correct=0;
				if($type['checkbox']==0) $correct=1;
				else{
					for ($j=1; $sheet['tasks'][$i]['answer_count']>=$j ; $j++){
						if($sheet['tasks'][$i]['answers'][$j]['is_correct']==1) $correct=$correct+1;
					}
				}
				$temp[1].= '(\''.$task_sheet_id.'\',\''.$i.'\',\''.$sheet['tasks'][$i]['question'].'\',\''.$type['button'].'\',\''.$type['checkbox'].'\',\''.$type['upload'].'\',\''.$sheet['tasks'][$i]['answer_count'].'\',\''.$correct.'\',\''.$sheet['tasks'][$i]['score'].'\'),';
			}
			$temp[1][strlen($temp[1])-1]='';

			//beilleszt task
			try{
				$stmt = $this->db->sqldb->prepare($temp[1]);
				$stmt->execute();
			}catch(PDOException $error){
				$error[1]=$error;
			}
			
			//felépít answer
			if($sheet['sheet_type'] != 'upload'){
				$task_query = $this->db->query("SELECT id , number FROM task WHERE sheet_id=?", array($task_sheet_id), $fetch = FALSE, $bindType = TRUE);
				for ($i=0; $task_query[$i]['id'] != NULL ; $i++){
					$taks_query_sort[$task_query[$i]['number']] = $task_query[$i]['id'];
				}
				
				$temp[2] = 'INSERT INTO `answer` (  `task_id` ,  `description` ,  `is_correct`   )   VALUES ';
				for ($i=1; $sheet['task_count']>=$i ; $i++){
					for ($j=1; $sheet['tasks'][$i]['answer_count']>=$j ; $j++){
						$temp[2].= '(\''.$taks_query_sort[$i].'\',\''.$sheet['tasks'][$i]['answers'][$j]['answer'].'\',\''.$sheet['tasks'][$i]['answers'][$j]['is_correct'].'\'),';
					}
				}
				$temp[2][strlen($temp[2])-1]='';
				
				//beilleszt answer
				try{
					$stmt = $this->db->sqldb->prepare($temp[2]);
					$stmt->execute();
				}catch(PDOException $error){
					$error[2]=$error;
				}
			}
			
			$sheet['step4']=1;
		}catch(PDOException $error){
			$db->rollBack();
		}
		return $task_sheet_id;
	}
	
	function FinalizeTaskSheetInSql($id){
		$temp = $this->db->query('UPDATE task_sheet SET is_ready=1 WHERE id= ?', array($id), $fetch = FALSE, $bindType = TRUE);
	}

	function InsertTaskAttachment($task_sheet_id, $task_id, $picture, $video=''){
		if($picture=='uploads/temp/') $picture = '';
		$stmt = $this->db->sqldb->prepare("INSERT INTO task_sheet_attachment (task_sheet_id, task_id, picture_url, video_url) VALUES (:task_sheet_id, :task_id, :picture_url, :video_url)");
			$stmt->bindParam(':task_sheet_id', $task_sheet_id);
			$stmt->bindParam(':task_id', $task_id);
			$stmt->bindParam(':picture_url', $picture);
			$stmt->bindParam(':video_url', $video);
			$stmt->execute();
			$arr = array();
			$arr = $stmt->errorInfo();
			return $arr;
	}
	
	function InsertUserAttachment($task_sheet_id, $task_id, $file){
		$stmt = $this->db->sqldb->prepare("INSERT INTO user_attachment (task_sheet_id, task_id, user_id, file) VALUES (:task_sheet_id, :task_id, :user_id, :file)");
			$stmt->bindParam(':task_sheet_id', $task_sheet_id);
			$stmt->bindParam(':task_id', $task_id);
			$stmt->bindParam(':user_id', $_SESSION['user_id']);
			$stmt->bindParam(':file', $file);
			$stmt->execute();
			$arr = array();
			$arr = $stmt->errorInfo();
			return $arr;
	}
		
	function SelectTaskSheetAttachmentFromSql($id){
		$temp = $this->db->query("SELECT * FROM task_sheet_attachment WHERE task_sheet_id= ?", array($id), $fetch = FALSE, $bindType = TRUE);
		return $temp;
	}
	
	function GetTaskNumberCountBySheetId($id){
		$temp = $this->db->query("SELECT number FROM task WHERE sheet_id= ?", array($id), $fetch = FALSE, $bindType = TRUE);
		return $temp;
	}
	
	function GetUserAttachment($tasksheet_id, $task_id){
		$temp = $this->db->query("SELECT file FROM user_attachment WHERE user_id=? AND task_sheet_id=? AND task_id=?", array( $_SESSION['user_id'], $tasksheet_id, $task_id), $fetch = FALSE, $bindType = TRUE);
		$attachment['url'] = $temp[0]['file'];
		$temp = explode('/', $attachment['url']);
		$attachment['name'] = $temp[count($temp)-1];
		return $attachment;
	}
	
	function GetUserAttachment2($tasksheet_id, $user_id){
		$temp = $this->db->query("SELECT file FROM user_attachment WHERE user_id=? AND task_sheet_id=?", array( $user_id, $tasksheet_id), $fetch = FALSE, $bindType = TRUE);
		$attachment['url'] = $temp[0]['file'];
		$temp = explode('/', $attachment['url']);
		$attachment['name'] = $temp[count($temp)-1];
		return $attachment;
	}
	
	function DeleteUserAttachment($tasksheet_id, $task_id){
		$temp = $this->db->query("DELETE FROM user_attachment WHERE user_id=? AND task_sheet_id=? AND task_id=?", array($_SESSION['user_id'], $tasksheet_id, $task_id ), $fetch = FALSE, $bindType = TRUE);
	}
	
	function ReallocateImage($temp_url, $path){
		$temp = explode('/', $temp_url);
		$picture_path = $path.$temp[count($temp)-1];
		rename($temp_url, $picture_path);
	}
	
	function SubmitAnswer($answers,$id){
		$i=0;
		foreach ($answers as $answersid){
			$answer_insert[$i]=$answersid;
			$i=$i+1;
		}
	//var_dump($answer_insert);
	//var_dump($id);
	
	$temp = 'INSERT INTO `user_answer` (  `user_id` ,  `answer_id` ) VALUES ';
	for ($j=0; $i>$j ; $j++){
		$temp.= '(\''.$id.'\',\''.$answer_insert[$j].'\'),';
	}
	$temp[strlen($temp)-1]='';
	try{
		$stmt = $this->db->sqldb->prepare($temp);
		$stmt->execute();
	}catch(PDOException $error){
		$error[0]=$error;
	}
	}
	
	function WhoCompleted($id){
		$temp = $this->db->query("SELECT user_id, file FROM user_attachment WHERE task_sheet_id=?", array($id), $fetch = FALSE, $bindType = TRUE);
		foreach($temp as $key => $element){
			$temp2 = $this->db->query("SELECT username FROM user WHERE id=?", array($element['user_id']), $fetch = FALSE, $bindType = TRUE);
			$temp[$key]['username']=$temp2[0]['username'];
			$t=explode('/', $element['file']);
			$temp[$key]['filename']=$t[count($t)-1];
			$temp2 = $this->db->query("SELECT scored_by, score FROM scores WHERE tasksheet_id=? AND user_id=?", array($id, $element['user_id']), $fetch = FALSE, $bindType = TRUE);
			if($temp2[0]['scored_by']==NULL){
			$temp[$key]['whorate']='-';
			}
			else{
			$temp[$key]['whorate']=$temp2[0]['scored_by'];
			}
			if($temp2[0]['score']==0){
			$temp[$key]['score']='-';
			}
			else{
			$temp[$key]['score']=$temp2[0]['score'];
			}
		}
		return $temp;
	}
	
	function DeleteTaskSheetFromSQL($id){
		$tasks = $this->db->query("SELECT id FROM task WHERE sheet_id=?", array($id), $fetch = FALSE, $bindType = TRUE);
		$temp = $this->db->query('DELETE FROM task_sheet WHERE id= ?', array($id), $fetch = FALSE, $bindType = TRUE);
		return $tasks;
	}
	
	function DeleteTaskFromSQL($id){	
		$answers = $this->db->query("SELECT id FROM answer WHERE task_id=?", array($id), $fetch = FALSE, $bindType = TRUE);
		$temp = $this->db->query('DELETE FROM task WHERE id= ?', array($id), $fetch = FALSE, $bindType = TRUE);
		return $answers;
	}

	function DeleteAnswerFromSQL($id){	
		$temp = $this->db->query('DELETE FROM answer WHERE id= ?', array($id), $fetch = FALSE, $bindType = TRUE);
		return $answers;
	}
	
	function InsertUploadRate($tasksheet_id, $user_id, $score, $scored_by, $comment){
		$stmt = $this->db->sqldb->prepare("INSERT INTO scores (tasksheet_id, user_id, score, scored_by, comment) VALUES (:tasksheet_id, :user_id, :score,  :scored_by, :comment)");
			$stmt->bindParam(':tasksheet_id', $tasksheet_id);
			$stmt->bindParam(':user_id', $user_id);
			$stmt->bindParam(':score', $score);
			$stmt->bindParam(':scored_by', $scored_by);
			$stmt->bindParam(':comment', $comment);
			$stmt->execute();
			$arr = array();
			$arr = $stmt->errorInfo();
			return $arr;
	}
	
	function AlreadySubmited($answer_ids,$user_id){
		$already = 0;
		for ($i=0;$i<count($answer_ids);$i++){
			for ($j=0;$j<count($answer_ids[$i]);$j++){
				$temp = $this->db->query("SELECT id FROM user_answer WHERE user_id=? AND answer_id=?", array($user_id, $answer_ids[$i][$j]["id"]), $fetch = FALSE, $bindType = TRUE);
				if (!empty($temp)) $already = 1;
			}
		}
		return $already;
	}
	
	function GetUploadRate($tasksheet_id, $user_id){
		$temp = $this->db->query("SELECT score, scored_by, comment FROM scores WHERE user_id=? AND tasksheet_id=?", array($user_id, $tasksheet_id), $fetch = FALSE, $bindType = TRUE);
		return $temp;
	}
	
	function UpdateUploadRate($tasksheet_id, $user_id, $score, $username, $comment){
		if($comment!=''){
		$temp = $this->db->query('UPDATE scores SET score=? , comment=?, scored_by=? WHERE tasksheet_id= ? AND user_id=?', array($score, $comment, $username, $tasksheet_id, $user_id), $fetch = FALSE, $bindType = TRUE);
		}
		else{
		$temp = $this->db->query('UPDATE scores SET score=? , scored_by=? WHERE tasksheet_id= ? AND user_id=?', array($score, $username, $tasksheet_id, $user_id), $fetch = FALSE, $bindType = TRUE);
		}
	}
	
	function MakeComment($username, $com, $prev=''){
		$comment=$prev.'<b>'.$username.'</b> hozzászólása:<br />'.date('Y-m-d H:i:s').$com.'<br/>';
		return $comment;
	}
	
	function GetMaxScore($tasksheet_id){
		$temp = $this->db->query("SELECT score FROM task WHERE sheet_id=?", array($tasksheet_id), $fetch = FALSE, $bindType = TRUE);
		return $temp;
	}
	
	function Edit_ChangeInfo($what,$id,$data){
		$temp = $this->db->query('UPDATE task_sheet SET name=? , total_score=? , expire=? WHERE id=?', array($data['name'],$data['score'],$data['expire'],$id), $fetch = FALSE, $bindType = TRUE);
	}
	
	function Edit_ChangeDescription($what,$id,$data){
		$temp = $this->db->query('UPDATE task SET description=? WHERE id=?', array($data['content'],$id), $fetch = FALSE, $bindType = TRUE);
	}
	
	function Edit_ModifyAnswer($what,$id,$data){
		//var_dump($data);
		$i=1;
		foreach($data as $key => $value){
			if(strpos($key,'correct_checkbox')!==FALSE){
				$checkbox[$i]=$value;
				unset($data[$key]);
				$i=$i+1;
			}
		}
		if($checkbox!=NULL){
			$temp = $this->db->query('UPDATE answer SET is_correct=0 WHERE task_id=?', array($id), $fetch = FALSE, $bindType = TRUE);
			for($i=1;$i<=count($checkbox);$i++){
				$temp = $this->db->query('UPDATE answer SET is_correct=1 WHERE id=?', array($checkbox[$i]), $fetch = FALSE, $bindType = TRUE);
			}
		}
		if($data['correct_radio']!=NULL){
			$correct_id = explode('-',$data['correct_radio']);
			$correct_id = $correct_id[1];
			$temp = $this->db->query('UPDATE answer SET is_correct=0 WHERE task_id=?', array($id), $fetch = FALSE, $bindType = TRUE);
			$temp = $this->db->query('UPDATE answer SET is_correct=1 WHERE id=?', array($correct_id), $fetch = FALSE, $bindType = TRUE);
		}
		foreach($data as $key => $value){
			if($key!='type' && $key!='correct_radio'){
				$answer_id = explode('-',$key);
				$answer_id = $answer_id[1];
				$temp = $this->db->query('UPDATE answer SET description=? WHERE id=?', array($value,$answer_id), $fetch = FALSE, $bindType = TRUE);
			}
		}
	}
	
	function Edit_NewAnswer($what,$id,$data){
		$stmt = $this->db->sqldb->prepare('INSERT INTO answer ( task_id , is_correct ) VALUES (:task_id , 0 )');		
		$stmt->bindParam(':task_id', $id);
		$stmt->execute();
	}
	
	function Edit_NewTask($what,$id,$data){
		$stmt = $this->db->sqldb->prepare('INSERT INTO task ( sheet_id , number , is_button , is_checkbox , is_upload , total_checkbox , correct_checkbox , score) VALUES (:sheet_id , :number , :is_button , :is_checkbox , 0 , 0 , 0 , 0)');		
		$stmt->bindParam(':sheet_id', $id);
		$stmt->bindParam(':number', $data['number']);
		$stmt->bindParam(':is_button', $data['is_button']);
		$stmt->bindParam(':is_checkbox', $data['is_checkbox']);
		$stmt->execute();
		$t_id = $this->db->query('SELECT id FROM task WHERE sheet_id=? AND number=? ', array($id,$data['number']), $fetch = FALSE, $bindType = TRUE);
		$t_id=$t_id[0]['id'];
		for($i=0;$i<2;$i++){
			$stmt = $this->db->sqldb->prepare('INSERT INTO answer ( task_id , is_correct ) VALUES (:task_id , 0 )');		
			$stmt->bindParam(':task_id', $t_id);
			$stmt->execute();
		}
	}
	
	function Edit_DeleteTask($what,$id,$data){
		$temp_answers = $this->db->query('SELECT id FROM answer WHERE task_id=?', array($id), $fetch = FALSE, $bindType = TRUE);
		$temp = $this->db->query('DELETE FROM task WHERE id=?', array($id), $fetch = FALSE, $bindType = TRUE);
		for($i=0;$i<count($temp_answers);$i++){
			$temp = $this->db->query('DELETE FROM answer WHERE id=?', array($temp_answers[$i]['id']), $fetch = FALSE, $bindType = TRUE);
		}
	}
	
	function Edit_DeleteAnswer($what,$id,$data){
		$temp = $this->db->query('DELETE FROM answer WHERE id=?', array($id), $fetch = FALSE, $bindType = TRUE);
	}	
	
}	
?>